package com.biosan.utils;

import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import net.sf.json.xml.XMLSerializer;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.JDOMException;
import org.jdom.input.SAXBuilder;

import com.biosan.DaoTnterface.JDataSet;

/**
 * BiosanUtils User: yzq Date: 2011-9-15 Time: 15:42:29
 */

public class BiosanUtils
{
    // private static ConnectionManager instance;
    /**
     * biosan common通用 数据库操作接口
     * @param list
     * @return
     * @throws IOException
     */

    public static QueryRunner getDb()
    {
        GetQueryRunner gr = GetQueryRunner.getQRunner();
        return gr.getQr();
    }

    public static QueryRunner getDb(String conString)
    {
        QueryRunner qr = null;

        try
        {
            qr = new QueryRunner(GetDataSource.getDataSource(conString));
        }
        catch (IOException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return qr;
    }

    public static List<Map<String, Object>> getListMapForsqlserver(String sql, Object[] params) throws SQLException
    {
        return getDb("sqlserver").query(sql, new MapListHandler(), params);
    }

    public static Map getMapForsqlserver(String sql, Object[] params) throws SQLException
    {
        log4j("").debug(sql+"="+ConvertToJson(params));
        return (Map) getDb("sqlserver").query(sql, new MapHandler(), params);
    }

    public static void sqlToexcell(String Savepath, String sheetname, String[] Headtitle, String sql, Object[] params)
    {
        log4j("").debug(sql+"="+ConvertToJson(params));
        JDataSet jSet = BiosanUtils.getDb(1).getJDataSet(sql, params, true, false);
        ExcellUtils excell = new ExcellUtils();
        excell.Toexcell(Savepath, sheetname, Headtitle, jSet);
    }

    /**
     * 应用于分页传入表名 来获取返回行数sum count(*)
     * @param sql from tale
     * @return
     */
    public static int getRowCount(String fromtable) throws SQLException
    {
        return ((Number) getDb().query("select count(*) from " + fromtable, new ScalarHandler())).intValue();
    }

    public static int getRowCount(String fromtable, Object[] params) throws SQLException
    {
        return ((Number) getDb().query("select count(*) from " + fromtable, new ScalarHandler(), params)).intValue();
    }

    /**
     * 应用于更新删除添加数据库操作
     * @param sql
     * @return
     */
    public static int executeUpdate(String sql, Object[] params)
    {
        log4j("").debug(sql+"="+ConvertToJson(params));
        int i = 0;
        try
        {
            i = getDb().update(sql, params);
        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return i;
    }

    public static int executeUpdateForsqlserver(String sql, Object[] params)
    {
        log4j("").debug(sql+"="+ConvertToJson(params));
        int i = 0;
        try
        {
            i = getDb("sqlserver").update(sql, params);
        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return i;
    }

    public static int SendMessage(String mobile, String Messagecontent, java.sql.Timestamp date) throws Exception
    {
        log4j("").debug(mobile+"="+Messagecontent);
        int i = 0;
        try
        {
            // String sqlString2="update SMSsend set mobile='13777574896'";
            String sqlString = "insert into SMSsend (mobile,content,SendTime) values (?,?,?)";
            Object params[] = { mobile, Messagecontent, date };
            i = getDb("sqlserver").update(sqlString, params);
        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return i;
    }

    /**
     * 应用于批量更新删除添加数据库操作
     * @param // String sql = "insert into employee(username,password)values(?,?)"; // String params[][] = {{ "123","12345"},{ "1234","1234567"}};
     * @return
     */
    public static int[] executeUpdateBatch(String sql, Object[][] params)
    {
        log4j("").debug(sql);
        int[] i = {};
        try
        {
            i = getDb().batch(sql, params);
        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return i;
    }

    public static int[] executeUpdateBatch(String sql, Object[][] params, String autoback) throws IOException, SQLException
    {
        int[] i = {};
        Connection conn = GetDataSource.getConnection();
        try
        {
            conn.setAutoCommit(false);
            i = getDb().batch(conn, sql, params);
            conn.commit();
            conn.close();
        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
            conn.rollback();
        }
        return i;
    }

    public static int[] executeUpdateBatchForsqlserver(String sql, Object[][] params)
    {
        log4j("").debug(sql);
        int[] i = {};
        try
        {
            i = getDb("sqlserver").batch(sql, params);
        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return i;
    }

    /**
     * 应用于sql 来获取返回行数sum avg count(*)
     * @param sql
     * @return
     */
    public static Object getSingle(String sql) throws SQLException
    {
        log4j("").debug(sql);
        return (Object) getDb().query(sql, new ScalarHandler());
    }

    @SuppressWarnings("deprecation")
    public static Object getSingle(String sql, Object[] params) throws SQLException
    {
        log4j("").debug(sql+"="+ConvertToJson(params));
        return (Object) getDb().query(sql, params, new ScalarHandler());
    }

    /**
     * 应用于oracle来获取返回自动增长的id
     * @param seq.nextval
     * @return
     */
    public static int getDualNEXTVAL(String SEQ) throws SQLException
    {

        return ((Number) getDb().query("select " + SEQ + " from dual", new ScalarHandler())).intValue();
    }

    /**
     * 应用于返回某一列【主键值】list key
     * @param sql 表名
     * @return
     */
    @SuppressWarnings("unchecked")
    public static List<String> getListColumn(String sql) throws SQLException
    {
        log4j("").debug(sql);
        return (List) getDb().query(sql, new ColumnListHandler());
    }

    public static List<String> getListColumn(String sql, Object[] params) throws SQLException
    {
        log4j("").debug(sql+"="+ConvertToJson(params));
        return (List) getDb().query(sql, params, new ColumnListHandler());
    }

    /**
     * 应用于根据关键key返回单个样本详情 count(*)
     * @param sql select book_name from book where id=“？”
     * @return
     */
    @SuppressWarnings("unchecked")
    public static Map getMap(String sql) throws SQLException
    {
        log4j("").debug(sql);
        return (Map) getDb().query(sql, new MapHandler());
    }

    public static Map getMap(String sql, Object[] params) throws SQLException
    { 
        log4j("").debug(sql+"="+ConvertToJson(params));
        return (Map) getDb().query(sql, new MapHandler(), params);
    }

    /**
     * 应用于根据条件搜索查询返回list<map>
     * @param sql select book_name from book where id=“？”
     * @return
     */
    public static List<Map<String, Object>> getListMap(String sql, Object[] params) throws SQLException
    {
        log4j("").debug(sql+"="+ConvertToJson(params));
        return getDb().query(sql, new MapListHandler(), params);
    }

    /**
     * 应用于分页
     * @param list
     * @return
     * @throws IOException
     */
    public static List<Map<String, Object>> getListMapAsPager(String columnName, String tableName, int currentPage, int pageSize, String orderBy,
            String sortorder, String andwhere) throws SQLException, IOException

    {
        
        String sql = getSqlStartEnd(columnName, tableName, currentPage, pageSize, orderBy, sortorder, andwhere);
        
        log4j("").debug("sql");
        return getDb().query(sql, new MapListHandler());
    }

    /**
     * 应用于分页
     * @param json
     * @return
     * @throws IOException
     */
    public static JSONArray getListMapAsPagerJson(String columnName, String tableName, int currentPage, int pageSize, String orderBy,
            String sortorder, String andwhere) throws SQLException, IOException

    {
        
        return ConvertToJson(getListMapAsPager(columnName, tableName, currentPage, pageSize, orderBy, sortorder, andwhere));

    }

    public static String getSqlStartEnd(String columnName, String tableName, int currentPage, int pageSize, String orderBy, String sortorder,
            String andwhere) throws IOException
    {
        int start = 1;
        if (getFileNode("/dbcp.properties", "jdbcUrl").startsWith("jdbc:oracle"))
        {

            start = 1;
            if (currentPage > 1)
                start = (currentPage - 1) * pageSize + 1;
        }
        if (getFileNode("/dbcp.properties", "jdbcUrl").startsWith("jdbc:mysql"))
        {
            start = 0;
            if (currentPage > 1)
                start = (currentPage - 1) * pageSize;
        }

        int end = start + pageSize - 1;
        String sql = "";
        String strwhere = "";
        String orderbyString = "";
        if (andwhere != null)
        {
            strwhere = " " + andwhere;
        }

        if (orderBy != null)

        {
            orderbyString = " order by " + orderBy + " " + sortorder;
        }
        if (getFileNode("/dbcp.properties", "jdbcUrl").startsWith("jdbc:oracle"))
        {
            sql = "select " + columnName + " from ( select rownum r,a.* from (select * from " + tableName + " " + orderbyString
                    + ") a where rownum<=" + end + strwhere + ") b where b.r>=" + start;
        }
        if (getFileNode("/dbcp.properties", "jdbcUrl").startsWith("jdbc:mysql"))
        {
            sql = "select " + columnName + " from " + tableName + " where " + strwhere + " " + orderbyString + " limit " + start + "," + pageSize;

        }

        if (start == -1 && end == -1)

        {
            sql = "select " + columnName + " from " + tableName;
        }

        // if (orderBy != null)
        //
        // {
        // sql += " order by " + orderBy + " " + sortorder;
        // }

        return sql;
    }

    /**
     * biosan 主要用于操作存储过程以及分页处理
     * @param list
     * @return
     */
    public static DButils getDb(int i)
    {
        DButils db = new DButils();
        return db;
    }

    /**
     * 将javad对象转换成json
     * @param list
     * @return
     */
    public static JSONArray ConvertToJson(Object strjava)
    {

        return JSONArray.fromObject(strjava);
    }

    /**
     * 将jdataset转换成json
     * @param list
     * @return
     */
    public static String ConvertToJson(JDataSet jdataset)
    {
        StringBuilder strbuild = new StringBuilder();
        strbuild.append("[");
        int Temprows = jdataset.getRowCount();
        int tempColumn = jdataset.getColumnCount();
        for (int i = 0; i < Temprows; i++)
        {
            strbuild.append("{");
            for (int j = 0; j < tempColumn; j++)
            {
                strbuild.append("\"" + jdataset.getColumnNames()[j] + "\"" + ":" + "\"" + jdataset.getCell(i, j) + "\"");
                if (j < tempColumn - 1)
                {
                    strbuild.append(",");
                }
            }

            strbuild.append("}");
            if (i < Temprows - 1)
            {
                strbuild.append(",");
            }

        }
        strbuild.append("]");
        return strbuild.toString();
    }

    /**
     * 将json转换成xml
     * @param list
     * @return
     */

    public static String JsonToXml(String jsonstring)
    {
        JSONObject json = JSONObject.fromObject(jsonstring);
        XMLSerializer xml = new XMLSerializer();
        return xml.write(json);
    }

    /**
     * 将list转换成Array
     * @param list
     * @return
     */
    public static Object[] getObjectArrayFromList(List list)
    {
        JSONArray jsonArray = JSONArray.fromObject(list);
        return jsonArray.toArray();
    }

    public java.sql.Timestamp dateTosqldate(String dateString, Date nowDate)
    {
        SimpleDateFormat f = null;
        if (dateString.contains(":"))
        {
            f = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        }
        else
        {

            f = new SimpleDateFormat("yyyy-MM-dd");
        }

        Long temp = null;
        Timestamp ts = null;
        if (dateString.equals("") == false)
        {
            try
            {
                temp = f.parse(dateString).getTime();
            }
            catch (ParseException e)
            {
                e.printStackTrace();
                log4j("").error(e);
            }
            ts = new Timestamp(temp);
        }

        return ts;
    }
    public static java.sql.Date StringToSqlDate(String datestr, String formatstr) throws Exception
    {
        java.sql.Date sqlDate = null;
        SimpleDateFormat bartDateFormat = new SimpleDateFormat(formatstr);
        try
        {
            java.util.Date date = bartDateFormat.parse(datestr);
            sqlDate = new java.sql.Date(date.getTime());
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return sqlDate;
    }

    /**
     * Get the formatted date string
     * @param date
     * @param format 'yyyy-MM-dd'
     * @return formatted date string
     */
    public static String DateToFormateString(Date date, String format)
    {
        String buffer = " ";

        if (date == null)
            return buffer;

        DateFormat dateFormat = new java.text.SimpleDateFormat(format);
        buffer = dateFormat.format(date);

        return buffer;
    }

    /**
     * parse the long format date
     * @param longDate
     * @return parsed date
     */
    public static Date StringToLongDate(String longDate)
    {
        if (!(longDate != null && longDate.equals("") == false))
            return null;
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = null;
        try
        {
            date = dateFormat.parse(longDate);
        }
        catch (ParseException e)
        {
            e.printStackTrace();
        }
        return date;
    }

    /**
     * parse the short format date
     * @param shortDate
     * @return parsed date
     */
    public static Date StringToshortDate(String shortDate)
    {
        if (!(shortDate != null && shortDate.equals("") == false))
            return null;
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date date = null;
        try
        {
            date = dateFormat.parse(shortDate);
        }
        catch (ParseException e)
        {
            e.printStackTrace();
        }
        return date;
    }

    public static Logger log4j(String classname)
    {
        return Logger.getLogger(classname);
    }

    public static int getThreadSleep(String fileproperties, String nodename) throws IOException
    {
        Properties p = new Properties();
        p.load(BiosanUtils.class.getResourceAsStream(fileproperties));
        return Integer.valueOf(p.getProperty(nodename));
    }

    public static String getFileNode(String fileproperties, String nodename) throws IOException
    {
        Properties p = new Properties();
        p.load(BiosanUtils.class.getResourceAsStream(fileproperties));
        return p.getProperty(nodename);
    }

    public static String Md5(String plainText)
    {
        String md5sString = "";
        try
        {
            MessageDigest md = MessageDigest.getInstance("MD5");
            md.update(plainText.getBytes());
            byte b[] = md.digest();
            int i;
            StringBuffer buf = new StringBuffer("");
            for (int offset = 0; offset < b.length; offset++)
            {
                i = b[offset];
                if (i < 0)
                    i += 256;
                if (i < 16)
                    buf.append("0");
                buf.append(Integer.toHexString(i));
            }

            // System.out.println("result: " + buf.toString());// 32位的加密

            // System.out.println("result: " + buf.toString().substring(8, 24));// 16位的加密
            md5sString = buf.toString();
        }
        catch (NoSuchAlgorithmException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
            md5sString = e.toString();
          //  System.out.println(md5sString);
            log4j("").error(e);
        }
        return md5sString;

    }

    public static java.sql.Date changeDateType(String dateString, Date nowDate)
    {
        DateFormat f = new SimpleDateFormat("yyyy-MM-dd");
        String temp = f.format(nowDate);
        java.sql.Date sd = null;
        try
        {
            if (dateString.equals("") == false)
                temp = dateString;
            Date d = f.parse(temp);
            sd = new java.sql.Date(d.getTime());
        }
        catch (ParseException e)
        {
            e.printStackTrace();
        }
        return sd;
    }

    /**
     * 转换timestamp
     */
    public static java.sql.Timestamp changeTimestampType(String dateString, Date nowDate)
    {
        SimpleDateFormat f = null;
        if (dateString.contains(":"))
        {
            f = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        }
        else
        {

            f = new SimpleDateFormat("yyyy-MM-dd");
        }

        Long temp = new Date().getTime();

        if (dateString.equals("") == false)
        {
            try
            {
                temp = f.parse(dateString).getTime();
            }
            catch (ParseException e)
            {
                e.printStackTrace();
            }
        }
        Timestamp ts = new Timestamp(temp);
        return ts;
    }

    public static Element readXML(String XmlPath)
    {
        SAXBuilder sb = new SAXBuilder();
        Document doc;
        Element result = null;
        try
        {
            doc = sb.build(new FileInputStream(XmlPath));
            Element el = doc.getRootElement();
            result = el;
        }
        catch (FileNotFoundException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
            result = null;
        }
        catch (JDOMException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
            result = null;
        }
        catch (IOException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
            result = null;
        }
        return result;

    }

    public static Element readXMLStrig(String stringXml)
    {
        Element result = null;
        SAXBuilder builder = new SAXBuilder(false);
        byte[] bytes;
        try
        {
            bytes = stringXml.getBytes("UTF-8");
            InputStream xmlInput = new ByteArrayInputStream(bytes);
            Document doc5;
            try
            {
                doc5 = builder.build(xmlInput);
                Element el5 = doc5.getRootElement();
                result = el5;
            }
            catch (JDOMException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
                result = null;
            }
            catch (IOException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
                result = null;
            }

        }
        catch (UnsupportedEncodingException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
            result = null;
        }

        return result;

    }

}
